// $codepro.audit.disable packageNamingConvention

/** Packages */
package edu.gatech.team404.dal;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.gatech.team404.Lab;
import edu.gatech.team404.Team;
import edu.gatech.team404.TeamAssignment;
import edu.gatech.team404.tool.Log;
import edu.gatech.team404.tool.Scheduler;

/**
 * A Data Access Layer for Lab.
 * @author Michael Dandy
 * @version 1.0
 */
public class LabDAL extends IDatabase {
	/** Class tag for logging purpose */
	private final String TAG = "LabDAL";
	
	/** The lab table name that this DAL is associated with */
	private final String LAB_TABLE = "LabTable";
	
	/** The lab course table name that this DAL is associated with */
	private final String LAB_COURSE_TABLE = "CourseLabTable";
	
	/** The team table name that this DAL is associated with */
	private final String TEAM_TABLE = "TeamTable";
	
	/** The lab team table name that this DAL is associated with */
	private final String LAB_TEAM_TABLE = "TeamIDTable";
	
	/**
	 * Create a lab.
	 * @param lab the lab to be created
	 * @return true on success or false otherwise
	 */
	public boolean createLab(Lab lab) {
		boolean ret = true;
		String query = "INSERT INTO " + LAB_TABLE + " VALUES (" 
						+ "\'" + lab.getId() + "\', " 
						+ "\'" + lab.getName() + "\', " 
						+ "\'" + lab.getDescription() + "\', " 
						+ "\'" + Scheduler.convertToString(lab.getStartDate()) + "\', " 
						+ "\'" + Scheduler.convertToString(lab.getEndDate()) + "\', " 
						+ "\'" + lab.getTeamAssignment().toString() + "\', " 
						+ "\'" + lab.getTeamSize() + "\')";
		Log.info(TAG, "Adding a lab ...");
		Log.info(TAG, query);
		ret &= executeUpdate(query);
		
		query = "INSERT INTO " + LAB_COURSE_TABLE + " VALUES (" 
				+ "\'" + lab.getCourseName() + "\', " 
				+ "\'" + lab.getId() + "\')";
		Log.info(TAG, "Assign a lab to course ...");
		Log.info(TAG, query);
		ret &= executeUpdate(query);
		
		return ret;
	}
	
	/**
	 * Parse team assignment method.
	 * @param teamAssignment the team assignment method
	 * @return the team assignment method type
	 */
	private TeamAssignment parseTeamAssignment (String teamAssignment) {
		if (teamAssignment.equalsIgnoreCase("Individual")) {
			return TeamAssignment.INDIVIDUAL;
		}
		else if (teamAssignment.equalsIgnoreCase("Random")) {
			return TeamAssignment.RANDOM;
		}
		else if (teamAssignment.equalsIgnoreCase("Self Select")) {
			return TeamAssignment.SELF_SELECT;
		}
		else {
			return TeamAssignment.UNKNOWN;
		}
	}
	
	/**
	 * Get the lab specified by the ID.
	 * @param labID the ID of the lab
	 * @return the lab with the specified ID
	 */
	public Lab getLabById(String labID) {
		String query = "SELECT * FROM " + LAB_COURSE_TABLE 
						+ " WHERE LabID = \'" + labID + "\'";
		ResultSet result = super.executeQuery(query);
		
		Log.info(TAG, "Retrieving course associated with lab ...");
		Log.info(TAG, query);
		
		if (result != null) {
			try {
				if (result.next()) {
					final String courseName = result.getString(1);
					
					query = "SELECT * FROM " + LAB_TABLE 
							+ " WHERE LabID = \'" + labID + "\'";
					result = super.executeQuery(query);
					
					Log.info(TAG, "Retrieving lab ...");
					Log.info(TAG, query);
					
					if (result != null && result.next()) {
						final Lab lab = new Lab (courseName, result.getString(1), 
											result.getString(2), result.getString(3), 
											Scheduler.convertToDate(result.getString(4)), 
											Scheduler.convertToDate(result.getString(5)), 
											parseTeamAssignment(result.getString(6)),
											result.getInt(7));
						return lab;
					}
				}
			} 
			catch (SQLException e) {
				Log.error(TAG, e);
				return null;
			}
		}
		return null;
	}
	
	/**
	 * Get the lab specified by the course name and ID
	 * @param courseName the name of the course this lab is associated with
	 * @param labID the ID of the lab
	 * @return the lab specified by the course name and ID
	 */
	public Lab getLab(String courseName, String labID) {
		String query = "SELECT * FROM " + LAB_COURSE_TABLE 
						+ " WHERE LabID = \'" + labID + "\'";
		ResultSet result = super.executeQuery(query);
		
		Log.info(TAG, "Retrieving course associated with lab ...");
		Log.info(TAG, query);
		
		if (result != null) {
			try {
				if (result.next()) {
					if (result.getString(1).equalsIgnoreCase(courseName)) {
						query = "SELECT * FROM " + LAB_TABLE 
								+ " WHERE LabID = \'" + labID + "\'";
						result = super.executeQuery(query);
						
						Log.info(TAG, "Retrieving lab ...");
						Log.info(TAG, query);
						
						if (result != null && result.next()) {
							final Lab lab = new Lab (courseName, result.getString(1), 
											result.getString(2), result.getString(3), 
											Scheduler.convertToDate(result.getString(4)), 
											Scheduler.convertToDate(result.getString(5)), 
											parseTeamAssignment(result.getString(6)),
											result.getInt(7));
							return lab;
						}
					}
				} 
			}
			catch (SQLException e) {
				Log.error(TAG, e);
				return null;
			}
		}
		return null;
	}
	
	/**
	 * Get all labs in the specified course.
	 * @param courseName the name of the course
	 * @return all labs in the specified course
	 */
	public List<Lab> getAllLabsInCourse (String courseName) {
		final String query = "SELECT * FROM " + LAB_COURSE_TABLE 
					+ " WHERE CourseName = \'" + courseName + "\'";
		final ResultSet result = super.executeQuery(query);
		
		Log.info(TAG, "Retrieving course associated with lab ...");
		Log.info(TAG, query);
		
		if (result != null) {
			try {
				final List<Lab> labs = new ArrayList<Lab> ();
				while (result.next()) {
					labs.add(getLab(courseName, result.getString(2)));
				}
				return labs;
			} 
			catch (SQLException e) {
				Log.error(TAG, e);
				return null;
			}
		}
		return null;
	}
	
	/**
	 * Update the specified lab. Only name, description, start date, and
	 * due date, team assignment, and the team size can be modified.
	 * @param lab the lab to be updated
	 * @return true on success or false otherwise
	 */
	public boolean updateLab (Lab lab) {
		String query = "UPDATE " + LAB_TABLE + " SET ";
		String update = "";
		final Lab old = getLabById(lab.getId());
		
		if (!lab.getName().isEmpty() && 
			!old.getName().equalsIgnoreCase(lab.getName())) {
			update += "Name=\'" + lab.getName() + "\'";
		}
		if (!lab.getDescription().isEmpty() && 
			!old.getDescription().equalsIgnoreCase(lab.getDescription())) {
			if (!update.isEmpty()) {
				update += ", ";
			}
			update += "Description=\'" + lab.getDescription() + "\'";
		}
		if (lab.getStartDate() != null && 
			!old.getStartDate().equals(lab.getStartDate())) {
			if (!update.isEmpty()) {
				update += ", ";
			}
			update += "StartDate=\'" 
					+ Scheduler.convertToString(lab.getStartDate()) + "\'";
		}
		if (lab.getEndDate() != null && 
			!old.getEndDate().equals(lab.getEndDate())) {
			if (!update.isEmpty()) {
				update += ", ";
			}
			update += "EndDate=\'" + Scheduler.convertToString(lab.getEndDate()) + "\'";
		}
		if (lab.getTeamAssignment() != TeamAssignment.UNKNOWN && 
			!old.getTeamAssignment().equals(lab.getTeamAssignment())) {
			if (!update.isEmpty()) {
				update += ", ";
			}
			update += "TeamAssignment=\'" + lab.getTeamAssignment().toString() + "\'";
		}
		if (lab.getTeamSize() != old.getTeamSize()) {
			if (!update.isEmpty()) {
				update += ", ";
			}
			update += "TeamSize=\'" + lab.getTeamSize() + "\'";
		}

		query += update;
		query += " WHERE LabID=\'" + lab.getId() + "\'";
		
		Log.info(TAG, "Updating account ...");
		Log.info(TAG, query);
		return super.executeUpdate(query);
	}
	
	/**
	 * Delete the specified lab.
	 * @param labID the lab ID
	 * @return true on success or false otherwise
	 */
	public boolean deleteLab (String labID) {
		boolean ret = true;
		
		final TeamDAL dal = new TeamDAL ();
		final List<Team> teams = dal.getTeamsInLab(labID);
		
		String query = "DELETE FROM " + LAB_COURSE_TABLE 
						+ " WHERE LabID=\'" + labID + "\'";
		
		Log.info(TAG, "Removing lab from course ...");
		Log.info(TAG, query);
		ret &= super.executeUpdate(query);
		
		query = "DELETE FROM " + LAB_TABLE 
				+ " WHERE LabID=\'" + labID + "\'";

		Log.info(TAG, "Removing a lab ...");
		Log.info(TAG, query);
		ret &= super.executeUpdate(query);
		
		query = "DELETE FROM " + LAB_TEAM_TABLE 
		+ " WHERE LabID=\'" + labID + "\'";
		
		Log.info(TAG, "Removing teams in lab ...");
		Log.info(TAG, query);
		ret &= super.executeUpdate(query);
		
		for (Team team : teams) {
			query = "DELETE FROM " + TEAM_TABLE 
					+ " WHERE TeamID=\'" + team.getTeamID() + "\'";
			
			Log.info(TAG, "Removing members in team ...");
			Log.info(TAG, query);
			ret &= super.executeUpdate(query);
		}
		
		return ret;
	}
	
	/**
	 * Close SQL connection
	 * @return true on success or false otherwise
	 */
	public boolean close() {
		return super.close();
	}
}
